package com.ideal.tools.db;

import com.ideal.service.Utils;
import com.ideal.tools.ssh.common.PropertyBox;
import com.ideal.tools.ssh.common.PropertyDictory;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;

import javax.rmi.CORBA.Util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by CC on 2016/7/25.
 */
public class MysqlDBUtils {
//    private static Map<String,MysqlDataSource> dataSources = new HashMap<String, MysqlDataSource>();

    /**
     * 载入数据库中的配置参数
     * @return
     */
    public static Map<String ,String> initDBProperty(){
        Map<String,String> result = new HashMap<String,String>();

        String sql = PropertyBox.getVal(PropertyDictory.PROPERTY_INIT_MYSQL_PARAMETER_SQL,
                "select parameterkey as pkey,parameterval as pval from cluster_parameter");
        //默认从websql 中取

        List<Map<String , Object>> list = query(sql,getWebAppMysqlConnector(true));
        if(null == list){
            return result;
        }
        for(Map<String,Object> row : list){
            Object key = row.get("pkey");
            Object val = row.get("pval");
            if (key==null) continue;
            if(val==null)
                result.put(key.toString(),"");
            else result.put(key.toString(),val.toString());
        }
        return result;
    }

    /**
     * 这个方法会批量执行file文件中的sql语句
     * 这里会剔除file中的select 语句，因为 返回结果不好返回
     * @return
     */
    public static int[] exeSQLFileInWebDB(String sqlFilePath){
        QueryRunner runner = new QueryRunner();
        int[] result=null;
        Connection connection=getWebAppMysqlConnector(false);

        try {
            //解析文件
            List<String> sqls= Utils.loadSql(sqlFilePath);
            //这里是原始文件
            result= new int[sqls.size()];
            int count=0;
            for(String sql : sqls){
                if (!sql.trim().toLowerCase().startsWith("select")) {
                    result[count]=runner.update(connection,sql);
                }else{
                    result[count]=1;
                }
            }
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 查询 web 数据库
     * @param sql
     * @return
     */
    public static List<Map<String,Object>> queryWebDB(String sql ){
        return queryWebDB(sql,true);
    }

    public static List<Map<String,Object>> queryWebDB(String sql ,boolean auto){
        return query(sql,getWebAppMysqlConnector(auto));
    }

    /**
     * 更新 web 数据库
     * @param sql
     * @return
     */
    public static int updateWebDB(String sql ){
        return updateWebDB(sql,true);
    }

    public static int updateWebDB(String sql ,boolean auto){
        return update(sql,getWebAppMysqlConnector(auto));
    }

    /**
     * 查询hive数据库
     * @param sql
     * @return
     */
    public static List<Map<String,Object>> queryHiveDB(String sql){
        return queryHiveDB(sql,true);
    }

    public static List<Map<String,Object>> queryHiveDB(String sql,boolean auto){
        return  query(sql, getHiveMysqlConnector(auto));
    }

    /**
     * 更新 hive 数据库
     * @param sql
     * @return
     */
    public static int updateHiveDB(String sql){
        return updateHiveDB(sql,true);
    }

    public static int updateHiveDB(String sql,boolean autocommit){
        return update(sql,getHiveMysqlConnector(autocommit));
    }
    /**
     * 批量在web执行语句
     * @param sql
     * @param objects
     * @return
     */
    public static int[] batchWebDB(String sql,Object[][] objects){
        return batchWebDB(sql,objects,true);
    }

    public static int[] batchWebDB(String sql,Object[][] objects,boolean autocmmit){
        return batch(sql,objects,getWebAppMysqlConnector(autocmmit));
    }

    /**
     * 批量在hive执行语句
     * @param sql
     * @param objects
     * @return
     */
    public static int[] batchHiveDB(String sql,Object[][] objects){
        return batchHiveDB(sql,objects,true);
    }

    public static int[] batchHiveDB(String sql,Object[][] objects,boolean autoCommit){
        return batch(sql,objects,getHiveMysqlConnector(autoCommit));
    }


//    private static boolean isSelect(String sql){
//        return sql.trim().toLowerCase().startsWith("select");
//    }
//    /**
//     *
//     * @param sql
//     * @param ds
//     * @return
//     */
//    private static Object query_update(String sql,boolean isSelect,MysqlDataSource ds){
//        //这里的判断比较简单，不知道是否考虑全面
////        sql = sql.trim();
////        sql.toLowerCase();
//        if(isSelect)
//            return query(sql,ds);
//        else
//            return update(sql,ds);
//    }

    /**
     * 查询
     * @param sql
     * @param ds mysql 元数据
     * @return
     */
    private static List<Map<String,Object>> query(String sql,MysqlDataSource ds){
        QueryRunner runner  = new QueryRunner(ds);
        List<Map<String , Object>> list = null;
        try {

            list= runner.query(sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void closeDBConnector(Connection connection){
        if(connection!=null)
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }

    /**
     * 使用connection 来查询
     * @param sql
     * @param connection
     * @return
     */
    private static List<Map<String,Object>> query(String sql,Connection connection){
        QueryRunner runner  = new QueryRunner();
        List<Map<String , Object>> list = null;
        try {
            list= runner.query(connection,sql,new MapListHandler());
            if(list!=null)
                System.out.println("MysqlDB query sql["+sql+"]: result["+list.size()+"]");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeDBConnector(connection);
        }
        return list;
    }

    /**
     * 执行非select 语句
     * @param sql
     * @param ds
     * @return
     */
    private static int update(String sql,MysqlDataSource ds){
        QueryRunner runner  = new QueryRunner(ds);
        try {
            return  runner.update(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    private static int update(String sql,Connection connection){
        QueryRunner runner  = new QueryRunner();
        try {
            return  runner.update(connection,sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeDBConnector(connection);
        }
        return -1;
    }

    private static int[] batch(String sql,Object[][] params,MysqlDataSource ds){
        QueryRunner runner  = new QueryRunner(ds);
        try {
            return runner.batch(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private static int[] batch(String sql,Object[][] params,Connection connection){
        QueryRunner runner  = new QueryRunner();
        try {
            return runner.batch(connection,sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeDBConnector(connection);
        }
        return null;
    }



//    /**
//     * 获取web 数据库信息
//     * @return
//     */
//    private static MysqlDataSource getWebAppMysqlDataSource(boolean autoCommit){
//        DBproperty dBproperty=new DBproperty( PropertyBox.getVal(PropertyDictory.WEB_APP_DB_IP,"")
//                ,PropertyBox.getVal(PropertyDictory.WEB_APP_DB_USER,"")
//                ,PropertyBox.getVal(PropertyDictory.WEB_APP_DB_PW,"")
//                ,PropertyBox.getVal(PropertyDictory.WEB_APP_DB_DB,"")
//                ,PropertyBox.getVal(PropertyDictory.WEB_APP_DB_PORT,"3306"));
//
//        return getDBSource(dBproperty,autoCommit);
//    }

    private static Connection getWebAppMysqlConnector(boolean autoCommit){
        DBproperty dBproperty=new DBproperty( PropertyBox.getVal(PropertyDictory.WEB_APP_DB_IP,"")
                ,PropertyBox.getVal(PropertyDictory.WEB_APP_DB_USER,"")
                ,PropertyBox.getVal(PropertyDictory.WEB_APP_DB_PW,"")
                ,PropertyBox.getVal(PropertyDictory.WEB_APP_DB_DB,"")
                ,PropertyBox.getVal(PropertyDictory.WEB_APP_DB_PORT,"3306"));
        return getDBConnector(dBproperty,autoCommit);
    }


//    /**
//     * 获取HIVE 数据库信息
//     * @return
//     */
//    private static MysqlDataSource getHiveMysqlDataSource(boolean autoCommit){
//        DBproperty dBproperty=new DBproperty( PropertyBox.getVal(PropertyDictory.HIVE_META_IP,"")
//                ,PropertyBox.getVal(PropertyDictory.HIVE_META_USER,"")
//                ,PropertyBox.getVal(PropertyDictory.HIVE_META_PW,"")
//                ,PropertyBox.getVal(PropertyDictory.HIVE_META_DB,"")
//                ,PropertyBox.getVal(PropertyDictory.HIVE_META_PORT,"3306"));
//
//        return getDBSource(dBproperty,autoCommit);
//    }

    private static Connection getHiveMysqlConnector(boolean autoCommit){
        DBproperty dBproperty=new DBproperty( PropertyBox.getVal(PropertyDictory.HIVE_META_IP,"")
                ,PropertyBox.getVal(PropertyDictory.HIVE_META_USER,"")
                ,PropertyBox.getVal(PropertyDictory.HIVE_META_PW,"")
                ,PropertyBox.getVal(PropertyDictory.HIVE_META_DB,"")
                ,PropertyBox.getVal(PropertyDictory.HIVE_META_PORT,"3306"));

        return getDBConnector(dBproperty, autoCommit);
    }

//    private static MysqlDataSource getDBSource(DBproperty dBproperty,boolean autoCommit){
//        String key = dBproperty.getPropertyKey();
//        MysqlDataSource ds=dataSources.get(key);
//
//        if (ds == null) {
//            ds = new MysqlDataSource();
//            ds.setServerName(dBproperty.getIp());
//            ds.setUser(dBproperty.getUsername());
//            ds.setPassword(dBproperty.getPassword());
//            ds.setDatabaseName(dBproperty.getDbname());
//            ds.setPort(Integer.parseInt(dBproperty.getProt()));
//            dataSources.put(key+"_"+autoCommit,ds);
//        }
//        return ds;
//    }

    private static Connection getDBConnector(DBproperty dBproperty,boolean autoCommit){
        String url="jdbc:mysql://"+dBproperty.getIp()+":"+dBproperty.getProt()+"/"+dBproperty.getDbname();
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, dBproperty.getUsername(), dBproperty.getPassword());
            connection.setAutoCommit(autoCommit);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }


    public static class DBproperty{
        String ip,username,password,dbname,prot;

        public DBproperty(String ip,String username,String password,String dbname,String prot){
            this.ip=ip;
            this.username=username;
            this.password=password;
            this.dbname=dbname;
            this.prot =prot;
        }


        public String getIp() {
            return ip;
        }

        public void setIp(String ip) {
            this.ip = ip;
        }

        public String getUsername() {
            return username;
        }

        public void setUsername(String username) {
            this.username = username;
        }

        public String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }

        public String getDbname() {
            return dbname;
        }

        public void setDbname(String dbname) {
            this.dbname = dbname;
        }

        public String getProt() {
            return prot;
        }

        public void setProt(String prot) {
            this.prot = prot;
        }

        public String getPropertyKey(){
            return  ip+":"+username+":"+password+":"+dbname+":"+prot;
        }
    }

}
